package com.taikang.base.util;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;

/**
 * 操作Excel工具类
 * @creator     taikang
 * @create-time 2016年4月14日   上午9:30:24
 * @email taikang@taikang.com
 * @company www.taikang.com
 * @version 1.0
 */
public class PoiUtil {
	
	/**
	 * 获取单元格中的值
	 * @param cell
	 * @return
	 */
	public static String getCellValue(HSSFCell cell) {
		String val = null;
		if(null != cell){
			int cellType = cell.getCellType();
			switch(cellType){
			case HSSFCell.CELL_TYPE_BLANK:
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				if(HSSFDateUtil.isCellDateFormatted(cell)){
					val = DateUtil.formatDate(cell.getDateCellValue(),"yyyy.M.d").trim();
				}else{
					val =cell.getCellFormula().trim();
				}
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				val = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if(HSSFDateUtil.isCellDateFormatted(cell)){
					val = DateUtil.formatDate(cell.getDateCellValue(),"yyyy.M.d").trim();
				}else{
					BigDecimal bigDecimal = new BigDecimal(cell.getNumericCellValue());
					val = bigDecimal.toPlainString();
				}
				break;
			case HSSFCell.CELL_TYPE_STRING:
				val = cell.getStringCellValue().trim();
				break;
			default:
					break;
			}
		}
		if(val != null){
			val = val.trim();
		}
		return val;
	}
	/**
	 * 获取单元格中的值
	 * @param cell
	 * @return
	 */
	@SuppressWarnings("static-access")
	public static String getCellValue(Cell cell) {
		if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {  
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {  
            return String.valueOf(cell.getNumericCellValue());  
        } else {  
            return String.valueOf(cell.getStringCellValue());  
        }  
	}
	 
	/**
	 * 创建Excel行头
	 * @param titles
	 * @param sheet
	 * @param headRowNumber
	 * @return
	 */
	public static Row createRow(String[] titles,Integer[] titleWidths,Workbook wb,Sheet sheet,Integer headRowNumber) {
        // 列头
		Row row = sheet.createRow(headRowNumber);
		if (titles != null && titles.length > 0 ){
			for (int i = 0; i < titles.length; i++) {
				sheet.setColumnWidth(i, titleWidths[i] * 256);
				Cell cell = row.createCell(i);
				cell.setCellValue(titles[i]);
				
				CellStyle cellStyle = wb.createCellStyle();
				// 指定单元格居中对齐
				cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
				// 指定单元格垂直居中对齐
				cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
				// 指定单元格自动换行
				cellStyle.setWrapText(true);

				// 设置单元格字体
				Font font = wb.createFont();
				font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
				cellStyle.setFont(font);

				// 设置单元格格式
				cell.setCellStyle(cellStyle);
			}
		}
		return row;
	}
	
	/**
	 * 导出Excel
	 * @param excelFileName excel文件名
	 * @param response
	 * @param wb
	 */
	public static void exportExcelData(String excelFileName,HttpServletResponse response,Workbook wb) {
		String fileName = new Date().getTime() + "";
		//获取文件名
		fileName = excelFileName + CommonUtil.formatDate(new Date(), "yyyyMMdd") + ".xls";
		OutputStream os = null;
		try {
			// 清空输出流
			response.reset();
			// 取得输出流
			os = response.getOutputStream(); 
			// 设定输出文件头
			response.setHeader("Content-disposition",
					"attachment; filename=" + new String(fileName.getBytes(), "ISO-8859-1"));
			// 定义输出类型
			response.setContentType("application/msexcel");
		} catch (IOException ex) {
			// 捕捉异常   
			ex.printStackTrace();
		}
		try {
			os.flush();
			wb.write(os);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 创建单元格
	 * @param wb
	 * @param row
	 * @param cellIndex
	 * @param value
	 */
	public static void createCellValue(HSSFWorkbook wb, HSSFRow row, int cellIndex, Object value) {
		String valueStr = String.valueOf(value);
		HSSFCell cell = row.createCell(cellIndex);
		cell.setCellValue(valueStr);

		// 设置格式
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 指定单元格自动换行
		cellStyle.setWrapText(true);
		// 设置单元格背景色
		// cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		// cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
		// cellStyle.setFillPattern(HSSFCellStyle.THICK_HORZ_BANDS);
		// 设置居中
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置单元格框的样式
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cell.setCellStyle(cellStyle);

		// 设置字体
		// HSSFFont font = wb.createFont();
		// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// cellStyle.setFont(font);
	}
}
